# -*- coding: utf-8 -*-
"""
清理user_doctor表中的重复记录
只保留每个user_id的最新一条记录
"""

import pymysql
from datetime import datetime

# 数据库配置
DB_CONFIG = {
    'host': 'localhost',
    'user': 'flask_dev',
    'password': 'dev123456',
    'database': 'flask_local',
    'charset': 'utf8mb4'
}

def clean_duplicate_doctors():
    """清理重复的医生信息，只保留最新的一条"""
    connection = None
    try:
        print('[INFO] 连接数据库...')
        connection = pymysql.connect(**DB_CONFIG)
        cursor = connection.cursor()
        
        # 1. 查询所有医生记录，按user_id分组
        print('\n[STEP 1] 查询所有医生记录...')
        cursor.execute("""
            SELECT user_id, COUNT(*) as count
            FROM user_doctor
            GROUP BY user_id
            HAVING count > 1
        """)
        duplicate_users = cursor.fetchall()
        
        if not duplicate_users:
            print('[OK] 没有发现重复记录！')
            return
        
        print(f'[INFO] 发现 {len(duplicate_users)} 个用户有重复记录:')
        for user_id, count in duplicate_users:
            print(f'  - user_id={user_id}: {count}条记录')
        
        # 2. 对每个有重复记录的用户进行清理
        total_deleted = 0
        for user_id, count in duplicate_users:
            print(f'\n[STEP 2] 清理 user_id={user_id} 的重复记录...')
            
            # 获取该用户的所有记录，按创建时间排序
            cursor.execute("""
                SELECT id, real_name, hospital, department, position, create_time
                FROM user_doctor
                WHERE user_id = %s
                ORDER BY create_time DESC
            """, (user_id,))
            records = cursor.fetchall()
            
            print(f'[INFO] 该用户共有 {len(records)} 条记录:')
            for idx, (id, name, hospital, dept, pos, create_time) in enumerate(records):
                status = '[KEEP]' if idx == 0 else '[DELETE]'
                print(f'  {status} id={id}, name={name}, hospital={hospital}, time={create_time}')
            
            # 保留第一条（最新的），删除其他
            keep_id = records[0][0]
            delete_ids = [r[0] for r in records[1:]]
            
            if delete_ids:
                placeholders = ','.join(['%s'] * len(delete_ids))
                cursor.execute(f"""
                    DELETE FROM user_doctor
                    WHERE id IN ({placeholders})
                """, delete_ids)
                
                deleted_count = cursor.rowcount
                total_deleted += deleted_count
                print(f'[OK] 删除了 {deleted_count} 条旧记录')
        
        # 3. 提交事务
        connection.commit()
        print(f'\n[SUCCESS] 清理完成！共删除 {total_deleted} 条重复记录')
        
        # 4. 验证结果
        print('\n[STEP 3] 验证清理结果...')
        cursor.execute("""
            SELECT user_id, COUNT(*) as count
            FROM user_doctor
            GROUP BY user_id
        """)
        final_results = cursor.fetchall()
        
        print('[INFO] 最终状态:')
        for user_id, count in final_results:
            cursor.execute("""
                SELECT real_name, hospital, create_time
                FROM user_doctor
                WHERE user_id = %s
            """, (user_id,))
            name, hospital, create_time = cursor.fetchone()
            print(f'  - user_id={user_id}: 1条记录 (name={name}, hospital={hospital}, time={create_time})')
        
    except pymysql.Error as e:
        print(f'[ERROR] 数据库操作失败: {e}')
        if connection:
            connection.rollback()
    except Exception as e:
        print(f'[ERROR] 发生错误: {e}')
        if connection:
            connection.rollback()
    finally:
        if connection:
            cursor.close()
            connection.close()
            print('\n[INFO] 数据库连接已关闭')

if __name__ == '__main__':
    print('=' * 60)
    print('清理user_doctor表重复记录脚本')
    print('=' * 60)
    
    confirm = input('\n确认要清理重复记录吗? (输入 yes 继续): ')
    if confirm.lower() == 'yes':
        clean_duplicate_doctors()
    else:
        print('[INFO] 操作已取消')

